Skip Headers

Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 31 Exchanging XML Data with Oracle Streams AQ

Oracle Streams Advanced Queuing (AQ) provides database integrated message queuing functionality:

- It enables and manages asynchronous communication of two or more applications using messages

- It supports point-to-point and publish/subscribe communication models

Integration of message queuing with Oracle Database brings the integrity, reliability, recoverability, scalability, performance, and security features of Oracle Database to message queuing. It also facilitates the extraction of intelligence from message flows.

This chapter describes how XML data can be exchanged using AQ. It contains these topics:

- How Do AQ and XML Complement Each Other?

- Oracle Streams and AQ

- XMLType Attributes in Object Types

- Internet Data Access Presentation (iDAP)

- iDAP Architecture

- Guidelines for Using XML and Oracle Streams Advanced Queuing

## How Do AQ and XML Complement Each Other?

XML has emerged as a standard format for business communications. XML is being used not only to represent data communicated between business applications, but also, the business logic that is encapsulated in the XML.

In Oracle Database, AQ supports native XML messages and also allows AQ operations to be defined in the XML-based Internet-Data-Access-Presentation (iDAP) format. iDAP, an extensible message invocation protocol, is built on Internet standards, using HTTP(S) and email protocols as the transport mechanism, and XML as the language for data presentation. Clients can access AQ using this.

### AQ and XML Message Payloads

Figure 31-1 shows an Oracle Database using AQ to communicate with three applications, with XML as the message payload. The general tasks performed by AQ in this scenario are:

- Message flow using subscription rules

- Message management

- Extracting business intelligence from messages

- Message transformation

This is an intra- and inter-business scenario where XML messages are passed asynchronously among applications using AQ.

- Intra-business. Typical examples of this kind of scenario include sales order fulfillment and supply-chain management.

- Inter-business processes. Here multiple integration hubs can communicate over the Internet backplane. Examples of inter-business scenarios include travel reservations, coordination between manufacturers and suppliers, transferring of funds between banks, and insurance claims settlements, among others.

  Oracle uses this in its enterprise application integration products. XML messages are sent from applications to an Oracle AQ hub. This serves as a message server for any application that wants the message. Through this hub-and-spoke architecture, XML messages can be communicated asynchronously to multiple loosely coupled receiving applications.

Figure 31-1 shows XML payload messages transported using AQ in the following ways:

- Web-based application that uses an AQ operation over an HTTP(S) connection using iDAP

- An application that uses AQ to propagate an XML message over a Net* connection

- An application that uses AQ to propagate an Internet or XML message directly to the database over HTTP(S) or SMTP

The figure also shows that AQ clients can access data using OCI, Java, or PL/SQL.

Figure 31-1 Oracle Streams Advanced Queuing and XML Message Payloads

Description of "Figure 31-1 Oracle Streams Advanced Queuing and XML Message Payloads"

### AQ Enables Hub-and-Spoke Architecture for Application Integration

A critical challenge facing enterprises today is application integration. Application integration involves getting multiple departmental applications to cooperate, coordinate, and synchronize in order to carry out complex business transactions.

AQ enables hub-and-spoke architecture for application integration. It makes integrated solution easy to manage, easy to configure, and easy to modify with changing business needs.

### Messages Can Be Retained for Auditing, Tracking, and Mining

Message management provided by AQ is not only used to manage the flow of messages between different applications, but also, messages can be retained for future auditing and tracking, and extracting business intelligence.

Viewing Message Content with SQL Views

AQ also provides SQL views to look at the messages. These SQL views can be used to analyze the past, current, and future trends in the system.

### Advantages of Using AQ

AQ provides the flexibility of configuring communication between different applications.

## Oracle Streams and AQ

Oracle Streams (Streams) enables you to share data and events in a stream. The stream can propagate this information within a database or from one database to another. The stream routes specified information to specified destinations. This provides greater functionality and flexibility than traditional solutions for capturing and managing events, and sharing the events with other databases and applications.

Streams enables you to break the cycle of trading off one solution for another. It enable you to build and operate distributed enterprises and applications, data warehouses, and high availability solutions. You can use all the capabilities of Oracle Streams at the same time.

You can use Streams to:

- Capture changes at a database. You can configure a background capture process to capture changes made to tables, database schemas, or the entire database. A capture process captures changes from the redo log and formats each captured change into a logical change record (LCR). The database where changes are generated in the redo log is called the source database.

- Enqueue events into a queue. Two types of events may be staged in a Streams queue: LCRs and user messages. A capture process enqueues LCR events into a queue that you specify. The queue can then share the LCR events within the same database or with other databases. You can also enqueue user events explicitly with a user application. These explicitly enqueued events can be LCRs or user messages.

- Propagate events from one queue to another. These queues may be in the same database or in different databases.

- Dequeue events. A background apply process can dequeue events. You can also dequeue events explicitly with a user application.

- Apply events at a database. You can configure an apply process to apply all of the events in a queue or only the events that you specify. You can also configure an apply process to call your own PL/SQL subprograms to process events.

  The database where LCR events are applied and other types of events are processed is called the destination database. In some configurations, the source database and the destination database may be the same.

### Streams Message Queuing

Streams allows user applications to:

- Enqueue messages of different types

- Propagate messages are ready for consumption

- Dequeue messages at the destination database

Streams introduces a new type of queue that stages messages of `SYS.AnyData` type. Messages of almost any type can be wrapped in a `SYS.AnyData` wrapper and staged in `SYS.AnyData` queues. Streams interoperates with Advanced Queuing (AQ), which supports all the standard features of message queuing systems, including multiconsumer queues, publishing and subscribing, content-based routing, internet propagation, transformations, and gateways to other messaging subsystems.

See Also: Oracle Streams Concepts and Administration, and its Appendix A, "XML Schema for LCRs".

## XMLType Attributes in Object Types

You can create queues that use Oracle object types containing `XMLType` attributes. These queues can be used to transmit and store messages that are XML documents. Using `XMLType`, you can do the following:

- Store any type of message in a queue

- Store `d`ocuments internally as `CLOB` values

- Store `m`ore than one type of payload in a queue

- Query `XMLType` columns using functions like `existsNode`

- Specify the operators in subscriber rules or dequeue selectors

## Internet Data Access Presentation (iDAP)

You can access AQ over the Internet by using Simple Object Access Protocol (SOAP). Internet Data Access Presentation (iDAP) is the SOAP specification for AQ operations. iDAP defines XML message structure for the body of the SOAP request. An iDAP-structured message is transmitted over the Internet using transport protocols such as HTTP(S) and SMTP.

iDAP uses the `text/xml` content type to specify the body of the SOAP request. XML provides the presentation for iDAP request and response messages as follows:

- All request and response tags are scoped in the SOAP namespace.

- AQ operations are scoped in the iDAP namespace.

- The sender includes namespaces in iDAP elements and attributes in the SOAP body.

- The receiver processes iDAP messages that have correct namespaces; for the requests with incorrect namespaces, the receiver returns an invalid request error.

- The SOAP namespace has this value: `http://schemas.xmlsoap.org/soap/envelope/`

- The iDAP namespace has this value: `http://ns.oracle.com/AQ/schemas/access`

  See Also: Oracle Streams Advanced Queuing User's Guide and Reference

## iDAP Architecture

Figure 31-2 shows the following components needed to send HTTP(S) messages:

- A client program that sends XML messages, conforming to iDAP format, to the AQ Servlet. This can be any HTTP client, such as Web browsers.

- The Web server or `ServletRunner` which hosts the AQ servlet that can interpret the incoming XML messages, for example, Apache/Jserv or Tomcat.

- Oracle Server/Database. Oracle Streams AQ servlet connects to Oracle Database to perform operations on your queues.

Figure 31-2 iDAP Architecture for Performing AQ Operations Using HTTP(S)

Description of "Figure 31-2 iDAP Architecture for Performing AQ Operations Using HTTP(S)"

### XMLType Queue Payloads

You can create queues with payloads that contain `XMLType` attributes. These can be used for transmitting and storing messages that contain XML documents. By defining Oracle objects with `XMLType` attributes, you can do the following:

- Store more than one type of XML document in the same queue. The documents are stored internally as `CLOB` instances.

- Selectively dequeue messages with `XMLType` attributes using the SQL functions such as `existsNode` and `extract`.

- Define transformations to convert Oracle objects to `XMLType`.

- Define rule-based subscribers that query message content using `XMLType` methods such as `existsNode()` and `extract()`.

Example 31-1 XMLType and AQ: Creating a Table and Queue, and Transforming Messages

In the BooksOnline application, assume that the Overseas Shipping site represents an order using `SYS.XMLType`. The Order Entry site represents an order as an Oracle object, `ORDER_TYP`.

The Overseas queue table and queue are created as follows:

    BEGIN
      DBMS_AQADM.create_queue_table(
        queue_table        => 'OS_orders_pr_mqtab',
        comment            => 'Overseas Shipping MultiConsumer Orders queue table',
        multiple_consumers => TRUE,
        queue_payload_type => 'SYS.XMLTtype',
        compatible         => '8.1');
    END;
    BEGIN
      DBMS_AQADM.create_queue(queue_name   => 'OS_bookedorders_que',
                              queue_table  => 'OS_orders_pr_mqtab');
    END;

Because the representation of orders at the overseas shipping site is different from the representation of orders at the order-entry site, a transformation is applied before messages are propagated from the order entry site to the overseas shipping site.

    /* Add a rule-based subscriber for overseas shipping to the booked-orders
       queues with transformation. Overseas Shipping handles orders outside the US. */
    DECLARE
      subscriber AQ$_AGENT;
    BEGIN
      subscriber := AQ$_AGENT('Overseas_Shipping', 'OS.OS_bookedorders_que', null);
      DBMS_AQADM.add_subscriber(
        queue_name     => 'OE.OE_bookedorders_que',
        subscriber     => subscriber,
        rule           => 'tab.user_data.orderregion = ''INTERNATIONAL''',
        transformation => 'OS.OE2XML');
    END;

For more details on defining transformations that convert the type used by the order entry application to the type used by Overseas Shipping, see Oracle Streams Advanced Queuing User's Guide and Reference the section on Creating Transformations in Chapter 8.

Example 31-2 XMLType and AQ: Dequeuing Messages

Assume that an application processes orders for customers in Canada. This application can dequeue messages using the following procedure:

    /* Create procedure to enqueue into single-consumer queues: */
    CREATE OR REPLACE PROCEDURE get_canada_orders() AS
      deq_msgid             RAW(16);
      dopt                  DBMS_AQ.dequeue_options_t;
      mprop                 DBMS_AQ.message_properties_t;
      deq_order_data        SYS.XMLTtype;
      no_messages           EXCEPTION;
      PRAGMA EXCEPTION_INIT (no_messages, -25228);
      new_orders            BOOLEAN := TRUE;
    BEGIN
      dopt.wait := 1;
      /* Specify dequeue condition to select Orders for Canada */
      dopt.deq_condition :=
        'tab.user_data.extract(        ''/ORDER_TYP/CUSTOMER/COUNTRY/text()'').getStringVal()=''CANADA''';
      dopt.consumer_name : = 'Overseas_Shipping';
      WHILE (new_orders) LOOP
        BEGIN
          DBMS_AQ.dequeue(queue_name         => 'OS.OS_bookedorders_que',
                          dequeue_options    => dopt,
                          message_properties => mprop,
                          payload            => deq_order_data,
                          msgid              => deq_msgid);
          COMMIT;
          DBMS_OUTPUT.put_line('Order for Canada - Order: ' ||
                               deq_order_data.getStringVal());
        EXCEPTION
          WHEN no_messages THEN
            DBMS_OUTPUT.put_line (' ---- NO MORE ORDERS  ---- ');
            new_orders := FALSE;
        END;
      END LOOP;
    END;
    CREATE TYPE mypayload_type as OBJECT (xmlDataStream CLOB, dtd CLOB, pdf BLOB);

## Guidelines for Using XML and Oracle Streams Advanced Queuing

This section describes guidelines for using XML and Oracle Streams Advanced Queuing.

### Storing Oracle Streams AQ XML Messages with Many PDFs as One Record?

You can exchange XML documents between businesses using Oracle Streams Advanced Queuing, where each message received or sent includes an XML header, XML attachment (XML data stream), DTDs, and PDF files, and store the data in a database table, such as a `queuetable`. You can enqueue the messages into Oracle queue tables as one record or piece. Or you can enqueue the messages as multiple records, such as one record for XML data streams as `CLOB` type, one record for PDF files as `RAW` type, and so on. You can also then dequeue the messages.

You can achieve this in the following ways:

- By defining an object type with (`CLOB`, `RAW`, ...) attributes, and storing it as a single message.

- By using the AQ message grouping feature and storing it in multiple messages. Here the message properties are associated with a group. To use the message grouping feature, all messages must be the same payload type.

To specify the payload, first create an object type, for example:

    CREATE TYPE mypayload_type as OBJECT (xmlDataStream CLOB, dtd CLOB, pdf BLOB);

then store it as a single message.

### Adding New Recipients After Messages Are Enqueued

You can use the queue table to support message assignments. For example, when other businesses send messages to a specific company, they do not know who should be assigned to process the messages, but they know the messages are for Human Resources (HR) for example. Hence all messages will go to the HR supervisor. At this point, the message is enqueued in the queue table. The HR supervisor is the only recipient of this message, and the entire HR staff have been pre-defined as subscribers for this queue.

You cannot change the recipient list after the message is enqueued. If you do not specify a recipient list then subscribers can subscribe to the queue and dequeue the message. Here, new recipients must be subscribers to the queue. Otherwise, you have to dequeue the message and enqueue it again with new recipients.

### Enqueuing and Dequeuing XML Messages?

Oracle Streams AQ supports enqueuing and dequeuing objects. These objects can have an attribute of type `XMLType` containing an XML document, as well as other interested "factored out" metadata attributes that may be useful to send along with the message. Refer to the latest AQ document, Oracle Streams Advanced Queuing User's Guide and Reference to get specific details and see more examples.

### Parsing Messages with XML Content from Oracle Streams AQ Queues

You may want to parse messages with XML content, from an Oracle Streams AQ queue and then update tables and fields in an ODS (Operational Data Store), in other words you may want to retrieve and parse XML documents, then map specific fields to database tables and columns. To get metadata such as AQ enqueue or dequeue times, JMS header information, and so on, based on queries on certain XML tag values, the easiest way is by using Oracle XML Parser for Java and Java Stored Procedures in tandem with Oracle Streams AQ (inside Oracle Database).

- If you store XML as CLOBs then you can definitely search the XML using Oracle Text but this only helps you find a particular message that matches a criteria.

- To do aggregation operations over the metadata, view the metadata from existing relational tools, or use normal SQL predicates on the metadata, then having the data only stored as XML in a CLOB will not be good enough.

You can combine Oracle Text XML searching with some redundant metadata storage as factored out columns and use SQL that combines normal SQL predicates with an Oracle Text `contains` expression to have the best of both of these options.

See Also: Chapter 10, "Full-Text Search Over XML".

### Preventing the Listener from Stopping Until the XML Document Is Processed

When receiving XML messages from clients as messages you may be required to process them as soon as they come in. Each XML document could take say about 15 seconds to process. For PL/SQL, one procedure starts the listener and dequeues the message and calls another procedure to process the XML document and the listener could be held up until the XML document is processed. Meanwhile messages accumulate in the queue.

After receiving the message, you can submit a job using the `DBMS_JOB` package. The job will be invoked asynchronously in a different database session.

Oracle Database added PL/SQL callbacks in the Oracle Streams AQ notification framework. This allows you register a PL/SQL callback that is invoked asynchronously when a message shows up in a queue.

### Using HTTPS with AQ

To send XML messages to suppliers using HTTPS and get a response back, you can use Oracle Streams AQ Internet access functionality. You can enqueue and dequeue messages over HTTP(S) securely and transactionally using XML.

See Also: Oracle Streams Advanced Queuing User's Guide and Reference

### Storing XML in Oracle Streams AQ Message Payloads

You can store XML in Oracle Streams AQ message payloads natively other than having an ADT as the payload with `sys.xmltype` as part of the ADT. In Oracle9i release 2 (9.2) and higher you can create queues with payloads and attributes as `XMLType`.

### Comparing iDAP and SOAP

iDAP is the SOAP specification for AQ operations. iDAP is the XML specification for Oracle Streams AQ operations. SOAP defines a generic mechanism to invoke a service. iDAP defines these mechanisms to perform AQ operations.

iDAP in addition has the following key properties not defined by SOAP:

- Transactional behavior. You can perform AQ operations in a transactional manner. You transaction can span multiple iDAP requests.

- Security. All the iDAP operations can be done only by authorized and authenticated users.